SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 CREATE FUNCTION dbo.priorNAVDate
 (
      @date         DATETIME,
      @PortfolioID  VARCHAR(MAX ),
      @InstrumentID INT,
      @Lot          INT
 )
 RETURNS @tbl  TABLE (navDate   DATETIME
                  , expid  INT
                  , tradeDate DATETIME
				  , instrumentName VARCHAR(MAX	)
				  , annualInterest DECIMAL(38,2)
				  , Par DECIMAL(38,2)
				  , assetID VARCHAR(MAX	)
			--,      TradeAmount DECIMAL(38,2)
				  )
 AS
 BEGIN
      INSERT INTO @tbl
      
      SELECT     e.NAVDate
               , MAX(e.ExportId) expId
               , @date
               , i.InstrName
               , a.SettDiscPurchPrc + a.CoupRevAnnualPurch + a.PIKPurchAmt
               , a.EndAdjAmt
               , a.AssetId
      FROM       FSFundModel_VALCOMM.Reporting.export e
                 INNER JOIN FSFundModel_VALCOMM.Reporting.Asset a
                 ON         e.ExportId = a.ExportId
                 
                 INNER JOIN FSFundModel_VALCOMM.Reporting.Instrument i
                 ON         e.ExportId     = i.ExportId
                 AND        a.InstrumentId = i.InstrumentId
      
      
      
      
      WHERE      e.FundId = 'FSIC'
                 --AND e.NAVDate IS NULL
      AND        e.NAVDate IN(@date
                            , DATEADD(DAY, -1, @date))
      
      AND        a.InstrumentId = @InstrumentID
      AND        a.LotId        = @Lot
      AND        a.PortfolioId  = @PortfolioID
      
      GROUP BY   e.NAVDate
               , i.InstrName
               , a.EndAdjAmt
               , a.SettDiscPurchPrc + a.CoupRevAnnualPurch + a.PIKPurchAmt
               , a.AssetId;
      
      RETURN
 END

GO
